''''
create database db_ems;

use db_ems;

create table if not exists teacher (id  int primary key auto_increment,
                      name varchar(20),
                      age int,
                      sex char(6),
                      teacherId varchar(20));
USE db_ems;
create table if not exists student(id int primary key auto_increment,
                                   name varchar(20),
                                   age int,
                                   sex char(6),
                                   sno varchar(20) not null );
USE db_ems;
ALTER TABLE student  ADD COLUMN cId VARCHAR(20) DEFAULT NULL ;


create table if not exists student(id int primary key auto_increment,
                    name varchar(20),
                    age int,
                    sex char(6),
                    sno varchar(20) not null );

create table if not exists class (id int  auto_increment PRIMARY KEY ,
                                  classname varchar(20),
                                  classId varchar(20));

create table if not exists sourse( id int auto_increment primary key ,
                    name varchar(20),
                    sourseId varchar(20),
                    teacherId varchar(20));

create table if not exists score(id int auto_increment primary key ,
                  sourseId varchar(20),
                  sno varchar(20));
                  

ALTER  TABLE db_ems.app02_client DROP passWorld;
ALTER  table db_ems.app02_client ADD (
passWord VARCHAR(32),
email varchar(20),
passworld varchar(20),
sex varchar(20),
city varchar(20),
hobby varchar(20));               
                                  
'''


''' @author:gxj 2018/11 '''
import pymysql
from django.db import connection,transaction
import pymysql,logging



host='127.0.0.1'
port=3306
user='root'
passwd='adminroot'
db='db_ems'
LOGGER=logging.getLogger()
# def condb(host,port,user,passwad,db):
#     LOGGER=logging.getLogger()
#     try:
#         conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db)
#         cursor=conn.cursor()
#         return conn,cursor
#     except Exception as e:
#         LOGGER.error('数据库连接失败：%s' % e)


def createDb(sql):
    try:
        conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db)
        cursor=conn.cursor()
        # return conn,cursor
    except Exception as e:
        LOGGER.error('数据库连接失败：%s' % e)
    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        LOGGER.error("创建表失败：%s" % e)
    finally:
        cursor.close()
        conn.close()
    return True

# createDb()


def sqlwrite(sql):
    try:
        conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db)
        cursor = conn.cursor()
        # print('-------------------------------------------------')
    except Exception as e:
        LOGGER.error('数据库连接失败：%s' % e)
    try:
        cursor.execute(sql)
        conn.commit()
        return True
    except Exception as e:
        conn.rollback()
        LOGGER.error('数据写入失败：%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return True


def sqlread(sql):
    try:
        conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db)
        cursor=conn.cursor()
    except Exception as e:
        LOGGER.error('数据库连接失败：%s' % e)
    try:
        cursor.execute(sql)
        data = [dict((cursor.description[i][0],value) for i ,value in enumerate(row))
                     for row in cursor.fetchall()]
    except Exception as e:
        LOGGER.error('数据执行失败：%s' % e)
    finally:
        cursor.close()
        conn.close()
    return data


